"""
SQLite SOP Repository
====================

This module implements the SOP repository interface using SQLite database.
"""

import sqlite3
import logging
from typing import List, Optional
from datetime import datetime

from ..domain.sop import SOP
from ..domain.sop_repository import SOPRepository

logger = logging.getLogger(__name__)


class SQLiteSOPRepository(SOPRepository):
    """
    SQLite implementation of the SOP repository interface.
    
    Provides CRUD operations for SOP entities using SQLite database.
    """
    
    def __init__(self, db_path: str):
        """
        Initialize the SQLite SOP repository.
        
        Args:
            db_path: Path to the SQLite database file
        """
        self.db_path = db_path
        self._init_database()
    
    def _init_database(self):
        """Initialize the database table if it doesn't exist."""
        try:
            with sqlite3.connect(self.db_path) as conn:
                # Create table
                conn.execute('''
                    CREATE TABLE IF NOT EXISTS sop (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        subject TEXT NOT NULL,
                        sop TEXT NOT NULL,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        created_by TEXT NOT NULL
                    )
                ''')
                
                # Create indexes separately
                conn.execute('CREATE INDEX IF NOT EXISTS idx_sop_subject ON sop (subject)')
                conn.execute('CREATE INDEX IF NOT EXISTS idx_sop_created_at ON sop (created_at)')
                conn.execute('CREATE INDEX IF NOT EXISTS idx_sop_created_by ON sop (created_by)')
                
                conn.commit()
                logger.info("SOP database table initialized successfully")
        except Exception as e:
            logger.error(f"Failed to initialize SOP database: {e}")
            raise
    
    def save(self, sop: SOP) -> SOP:
        """Save a SOP entity to the repository."""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                if sop.id is None:
                    # Insert new SOP
                    cursor.execute('''
                        INSERT INTO sop (subject, sop, created_by)
                        VALUES (?, ?, ?)
                    ''', (sop.subject, sop.sop, sop.created_by))
                    
                    sop.id = cursor.lastrowid
                    
                    # Get the created_at timestamp
                    cursor.execute('SELECT created_at FROM sop WHERE id = ?', (sop.id,))
                    result = cursor.fetchone()
                    if result:
                        sop.created_at = datetime.fromisoformat(result[0])
                else:
                    # Update existing SOP
                    cursor.execute('''
                        UPDATE sop 
                        SET subject = ?, sop = ?, created_by = ?
                        WHERE id = ?
                    ''', (sop.subject, sop.sop, sop.created_by, sop.id))
                
                conn.commit()
                logger.info(f"SOP saved successfully: {sop}")
                return sop
                
        except Exception as e:
            logger.error(f"Failed to save SOP: {e}")
            raise
    
    def find_by_id(self, sop_id: int) -> Optional[SOP]:
        """Find a SOP by its ID."""
        try:
            with sqlite3.connect(self.db_path) as conn:
                conn.row_factory = sqlite3.Row
                cursor = conn.cursor()
                
                cursor.execute('SELECT * FROM sop WHERE id = ?', (sop_id,))
                row = cursor.fetchone()
                
                if row:
                    return self._row_to_sop(row)
                return None
                
        except Exception as e:
            logger.error(f"Failed to find SOP by ID {sop_id}: {e}")
            raise
    
    def find_all(self) -> List[SOP]:
        """Find all SOPs in the repository."""
        try:
            with sqlite3.connect(self.db_path) as conn:
                conn.row_factory = sqlite3.Row
                cursor = conn.cursor()
                
                cursor.execute('SELECT * FROM sop ORDER BY created_at DESC')
                rows = cursor.fetchall()
                
                return [self._row_to_sop(row) for row in rows]
                
        except Exception as e:
            logger.error(f"Failed to find all SOPs: {e}")
            raise
    
    def update(self, sop: SOP) -> SOP:
        """Update an existing SOP in the repository."""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                cursor.execute('''
                    UPDATE sop 
                    SET subject = ?, sop = ?, created_by = ?
                    WHERE id = ?
                ''', (sop.subject, sop.sop, sop.created_by, sop.id))
                
                if cursor.rowcount == 0:
                    raise ValueError(f"SOP with ID {sop.id} not found")
                
                conn.commit()
                logger.info(f"SOP updated successfully: {sop}")
                return sop
                
        except Exception as e:
            logger.error(f"Failed to update SOP: {e}")
            raise
    
    def delete(self, sop_id: int) -> bool:
        """Delete a SOP from the repository."""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                cursor.execute('DELETE FROM sop WHERE id = ?', (sop_id,))
                deleted = cursor.rowcount > 0
                
                conn.commit()
                if deleted:
                    logger.info(f"SOP deleted successfully: ID {sop_id}")
                else:
                    logger.warning(f"SOP not found for deletion: ID {sop_id}")
                
                return deleted
                
        except Exception as e:
            logger.error(f"Failed to delete SOP: {e}")
            raise
    
    def find_by_subject(self, subject: str) -> List[SOP]:
        """Find SOPs by subject (partial match)."""
        try:
            with sqlite3.connect(self.db_path) as conn:
                conn.row_factory = sqlite3.Row
                cursor = conn.cursor()
                
                cursor.execute('''
                    SELECT * FROM sop 
                    WHERE subject LIKE ? 
                    ORDER BY created_at DESC
                ''', (f'%{subject}%',))
                
                rows = cursor.fetchall()
                return [self._row_to_sop(row) for row in rows]
                
        except Exception as e:
            logger.error(f"Failed to find SOPs by subject '{subject}': {e}")
            raise
    
    def find_by_creator(self, created_by: str) -> List[SOP]:
        """Find SOPs by creator."""
        try:
            with sqlite3.connect(self.db_path) as conn:
                conn.row_factory = sqlite3.Row
                cursor = conn.cursor()
                
                cursor.execute('''
                    SELECT * FROM sop 
                    WHERE created_by = ? 
                    ORDER BY created_at DESC
                ''', (created_by,))
                
                rows = cursor.fetchall()
                return [self._row_to_sop(row) for row in rows]
                
        except Exception as e:
            logger.error(f"Failed to find SOPs by creator '{created_by}': {e}")
            raise
    
    def find_by_ids(self, sop_ids: List[int]) -> List[SOP]:
        """Find SOPs by their IDs."""
        try:
            if not sop_ids:
                return []
                
            with sqlite3.connect(self.db_path) as conn:
                conn.row_factory = sqlite3.Row
                cursor = conn.cursor()
                
                # Use parameterized query to prevent SQL injection
                placeholders = ','.join(['?' for _ in sop_ids])
                cursor.execute(f'''
                    SELECT * FROM sop 
                    WHERE id IN ({placeholders})
                    ORDER BY id
                ''', sop_ids)
                
                rows = cursor.fetchall()
                return [self._row_to_sop(row) for row in rows]
                
        except Exception as e:
            logger.error(f"Failed to find SOPs by IDs {sop_ids}: {e}")
            raise
    
    def _row_to_sop(self, row: sqlite3.Row) -> SOP:
        """Convert a database row to a SOP entity."""
        return SOP(
            id=row['id'],
            subject=row['subject'],
            sop=row['sop'],
            created_at=datetime.fromisoformat(row['created_at']),
            created_by=row['created_by']
        )